using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;

public partial class DatabaseReporting : System.Web.UI.Page
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //SelectListInstanceSearch
            this.SelectListInstance.Items.Add(new ListItem("BusinessCritical", "BusinessCritical"));
            this.SelectListInstance.Items.Add(new ListItem("BusinessCriticalLevel", "BusinessCriticalLevel"));            
            this.SelectListInstance.Items.Add(new ListItem("InstanceDomainName", "InstanceDomainName"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceShortDescription", "InstanceShortDescription"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceCollation", "InstanceCollation"));            
            this.SelectListInstance.Items.Add(new ListItem("InstanceOSVersion", "InstanceOSVersion"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceEdition", "InstanceEdition"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceVersion", "InstanceVersion"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceServicePack", "InstanceServicePack"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceMemory", "InstanceMemory"));            
            this.SelectListInstance.Items.Add(new ListItem("InstanceProcessorArchitecture", "InstanceProcessorArchitecture"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceProcessorName", "InstanceProcessorName"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceCoreCount", "InstanceCoreCount"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceIsClustered", "InstanceIsClustered"));
            this.SelectListInstance.Items.Add(new ListItem("IsVirtual", "IsVirtual"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceLoginMode", "InstanceLoginMode"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceMinMemory", "InstanceMinMemory"));
            this.SelectListInstance.Items.Add(new ListItem("InstanceMaxMemory", "InstanceMaxMemory"));            
            this.SelectListInstance.Items.Add(new ListItem("DashboardActive", "DashboardActive"));
            this.SelectListInstance.Items.Add(new ListItem("LoadRepository", "LoadRepository"));            
            this.SelectListInstance.Items.Add(new ListItem("InstancePortNumber", "InstancePortNumber"));
            this.SelectListInstance.Items.Add(new ListItem("LastSQLServerRestart", "LastSQLServerRestart"));
            this.SelectListInstance.Items.Add(new ListItem("PrimaryContactEmail", "PrimaryContactEmail"));
            this.SelectListInstance.Items.Add(new ListItem("TraceFlags", "TraceFlags"));
            this.SelectListInstance.Items.Add(new ListItem("SQLServerServiceAccount", "SQLServerServiceAccount"));
            this.SelectListInstance.Items.Add(new ListItem("SQLAgentServiceAccount", "SQLAgentServiceAccount"));
            
            //PreselectInstanceSelectList
            this.SelectListInstance.Items.FindByText("InstanceShortDescription").Selected = true;
            this.SelectListInstance.Items.FindByText("InstanceVersion").Selected = true;                

            //SelectListDatabaseSearch
            this.SelectList.Items.Add(new ListItem("Total Size", "TotalSizeMB"));
            this.SelectList.Items.Add(new ListItem("DataSizeMB", "DataSizeMB"));
            this.SelectList.Items.Add(new ListItem("LogSizeMB", "LogSizeMB"));
            this.SelectList.Items.Add(new ListItem("DataSpaceUsedMB", "DataSpaceUsedMB"));
            this.SelectList.Items.Add(new ListItem("DataSpaceAvailableMB", "DataSpaceAvailableMB"));
            this.SelectList.Items.Add(new ListItem("DataSpaceAvailablePct", "DataSpaceAvailablePct"));
            this.SelectList.Items.Add(new ListItem("LogSpaceUsedMB", "LogSpaceUsedMB"));
            this.SelectList.Items.Add(new ListItem("LogSpaceAvailableMB", "LogSpaceAvailableMB"));
            this.SelectList.Items.Add(new ListItem("LogSpaceAvailablePct", "LogSpaceAvailablePct"));
            this.SelectList.Items.Add(new ListItem("IndexSpaceReservedMB", "IndexSpaceReservedMB"));
            this.SelectList.Items.Add(new ListItem("IndexSpaceUsedMB", "IndexSpaceUsedMB"));
            this.SelectList.Items.Add(new ListItem("LastBackupFULL", "LastBackupFULL"));
            this.SelectList.Items.Add(new ListItem("LastBackupDIFF", "LastBackupDIFF"));
            this.SelectList.Items.Add(new ListItem("LastBackupLOG", "LastBackupLOG"));
            this.SelectList.Items.Add(new ListItem("DatabaseStatus", "DatabaseStatus"));
            this.SelectList.Items.Add(new ListItem("IsReadOnly", "IsReadOnly"));
            this.SelectList.Items.Add(new ListItem("RecoveryModel", "RecoveryModel"));
            this.SelectList.Items.Add(new ListItem("AutoShrink", "AutoShrink"));
            this.SelectList.Items.Add(new ListItem("AutoUpdateStatistics", "AutoUpdateStatistics"));
            this.SelectList.Items.Add(new ListItem("AutoCreateStatistics", "AutoCreateStatistics"));
            this.SelectList.Items.Add(new ListItem("AutoClose", "AutoClose"));
            this.SelectList.Items.Add(new ListItem("Trustworthy", "Trustworthy"));
            this.SelectList.Items.Add(new ListItem("Collation", "Collation"));
            this.SelectList.Items.Add(new ListItem("CompatibilityLevel", "CompatibilityLevel"));
            this.SelectList.Items.Add(new ListItem("PageVerify", "PageVerify"));
            this.SelectList.Items.Add(new ListItem("IsSensitive", "IsSensitive"));
            this.SelectList.Items.Add(new ListItem("IsAudited", "IsAudited"));
            this.SelectList.Items.Add(new ListItem("PrimaryContactEmail", "PrimaryContactEmail"));
           
            //Populate Dropdowns
            this.ddlInstanceDomainName.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(InstanceDomainName) FROM Instance");
            this.ddlInstanceDomainName.DataTextField = "InstanceDomainName";
            this.ddlInstanceDomainName.DataValueField = "InstanceDomainName";
            this.ddlInstanceDomainName.DataBind();

            this.ddlInstanceCollation.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(InstanceCollation) FROM Instance");
            this.ddlInstanceCollation.DataTextField = "InstanceCollation";
            this.ddlInstanceCollation.DataValueField = "InstanceCollation";
            this.ddlInstanceCollation.DataBind();

            this.ddlInstanceOSVersion.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(InstanceOSVersion) FROM Instance");
            this.ddlInstanceOSVersion.DataTextField = "InstanceOSVersion";
            this.ddlInstanceOSVersion.DataValueField = "InstanceOSVersion";
            this.ddlInstanceOSVersion.DataBind();

            this.ddlInstanceSQLVersion.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(InstanceVersion) FROM Instance");
            this.ddlInstanceSQLVersion.DataTextField = "InstanceVersion";
            this.ddlInstanceSQLVersion.DataValueField = "InstanceVersion";
            this.ddlInstanceSQLVersion.DataBind();

            this.ddlRecoveryModel.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(RecoveryModel)  FROM [Database]");
            this.ddlRecoveryModel.DataTextField = "RecoveryModel";
            this.ddlRecoveryModel.DataValueField = "RecoveryModel";
            this.ddlRecoveryModel.DataBind();

            this.ddlTrustworthy.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(Trustworthy) FROM [Database]");
            this.ddlTrustworthy.DataTextField = "Trustworthy";
            this.ddlTrustworthy.DataValueField = "Trustworthy";
            this.ddlTrustworthy.DataBind();

            this.ddlDatabaseCollation.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(Collation) FROM [Database]");
            this.ddlDatabaseCollation.DataTextField = "Collation";
            this.ddlDatabaseCollation.DataValueField = "Collation";
            this.ddlDatabaseCollation.DataBind();

            this.ddlDatabaseCompatibilityLevel.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(CompatibilityLevel) FROM [Database]");
            this.ddlDatabaseCompatibilityLevel.DataTextField = "CompatibilityLevel";
            this.ddlDatabaseCompatibilityLevel.DataValueField = "CompatibilityLevel";
            this.ddlDatabaseCompatibilityLevel.DataBind();

            this.ddlDatabasePageVerify.DataSource = db.ExecuteDataSet(CommandType.Text, "SELECT DISTINCT(PageVerify) FROM [Database]");
            this.ddlDatabasePageVerify.DataTextField = "PageVerify";
            this.ddlDatabasePageVerify.DataValueField = "PageVerify";
            this.ddlDatabasePageVerify.DataBind();

            this.tblSearchInstance.Visible = true;
            this.tblSearchDatabase.Visible = false;
        }

        this.lblResults.Text = "";
        this.txtInstanceName1.Visible = this.WhereInstanceName1.Checked;
        this.lblInstanceNameLike.Visible = this.WhereInstanceName1.Checked;
        this.lbInstanceDomainNameEqual.Visible = this.WhereInstanceDomain.Checked;
        this.lblInstanceCollationEqual.Visible = this.WhereInstanceCollation.Checked;
        this.ddlInstanceCollation.Visible = this.WhereInstanceCollation.Checked;
        this.lblInstanceOSVersionEqual.Visible = this.WhereInstanceOSVersion.Checked;
        this.ddlInstanceOSVersion.Visible = this.WhereInstanceOSVersion.Checked;
        this.lblInstanceSQLVersionEqual.Visible = this.WhereInstanceSQLVersion.Checked;
        this.ddlInstanceSQLVersion.Visible = this.WhereInstanceSQLVersion.Checked;
        this.lblInstanceIsClusteredEqual.Visible = this.WhereInstanceIsClustered.Checked;
        this.rdoInstanceIsClustered.Visible = this.WhereInstanceIsClustered.Checked;
        this.lblInstanceIsVirtual.Visible = this.WhereInstanceIsVirtual.Checked;
        this.rdoInstanceIsVirtual.Visible = this.WhereInstanceIsVirtual.Checked;

        this.lblDatabaseInstanceNameLike.Visible = this.WhereInstanceName2.Checked;
        this.txtInstanceName2.Visible = this.WhereInstanceName2.Checked;
        this.ddlInstanceDomainName.Visible = this.WhereInstanceDomain.Checked;
        this.lblDatabaseNameLike.Visible = this.WhereDatabaseName.Checked;
        this.txtDatabaseName.Visible = this.WhereDatabaseName.Checked;
        this.ddlDatabaseSizeMB.Visible = this.WhereSizeMB.Checked;
        this.lblDatabaseSizeIs.Visible = WhereSizeMB.Checked;
        this.lblDatabaseRevoveryModelEqual.Visible = WhereRecoveryModel.Checked;
        this.ddlRecoveryModel.Visible = WhereRecoveryModel.Checked;
        this.lblDatabaseTrustworthyEqual.Visible = this.WhereTrustworthy.Checked;
        this.ddlTrustworthy.Visible = this.WhereTrustworthy.Checked;
        this.lblDatabaseCollationEqual.Visible = this.WhereDatabaseCollation.Checked;
        this.ddlDatabaseCollation.Visible = this.WhereDatabaseCollation.Checked;
        this.lblDatabaseCompatibiliyLevelEqual.Visible = this.WhereDatabaseCompatibilityLevel.Checked;
        this.ddlDatabaseCompatibilityLevel.Visible = this.WhereDatabaseCompatibilityLevel.Checked;
        this.lblDatabasePageVerify.Visible = this.WhereDatabasePageVerify.Checked;
        this.ddlDatabasePageVerify.Visible = this.WhereDatabasePageVerify.Checked;
        this.lblAutoEqual.Visible = this.WhereAuto.Checked;
        this.ddlAuto.Visible = this.WhereAuto.Checked;
        this.rdoAuto.Visible = this.WhereAuto.Checked;
        this.ddlDatabaseSpaceAvailablePct.Visible = this.WhereFreeSpace.Checked;
        this.lblDatabaseSpaceAvailablePct.Visible = this.WhereFreeSpace.Checked;
        this.lblIsAudited.Visible = this.WhereDatabaseIsAudited.Checked;
        this.rdoIsAudited.Visible = this.WhereDatabaseIsAudited.Checked;
        this.lblIsSensitive.Visible = this.WhereDatabaseIsSensitive.Checked;
        this.rdoIsSensitive.Visible = this.WhereDatabaseIsSensitive.Checked;
        this.lblInstanceIsBusinessCritical.Visible = this.WhereInstanceIsBusinessCritical.Checked;
        this.rdoInstanceIsBusinessCritical.Visible = this.WhereInstanceIsBusinessCritical.Checked;
        
    }
    protected void gvResults_RowCreated(object sender, GridViewRowEventArgs e)
    {
        //hide a cell in the column
        e.Row.Cells[2].Visible = false;
        e.Row.Cells[3].Visible = false;
    }
    protected void gvResultsInstanceSearch_RowCreated(object sender, GridViewRowEventArgs e)
    {
        //hide a cell in the column
        e.Row.Cells[1].Visible = false;
    }
    protected void btnSearch_Click(object sender, ImageClickEventArgs e)
    {
        if (this.optSearchType.SelectedIndex == 0)
        {
            //SELECT Columns
            System.Text.StringBuilder sqlQuery = new System.Text.StringBuilder("SELECT InstanceName ");
            foreach (ListItem li in this.SelectListInstance.Items)
            {
                if (li.Selected)
                {
                    if (sqlQuery.Length > 0)
                        sqlQuery.Append(", ");
                    sqlQuery.Append(li.Value);
                }
            }

            sqlQuery.Append(" FROM [Instance] WITH(NOLOCK) WHERE 1 = 1");

            if (WhereInstanceName1.Checked == true)
            {
                sqlQuery.Append(" AND [InstanceName] like '%" + this.txtInstanceName1.Text.ToString() + "%'");
            }

            if (this.WhereInstanceIsBusinessCritical.Checked == true)
            {
                sqlQuery.Append(" AND [BusinessCritical] = " + this.rdoInstanceIsBusinessCritical.SelectedValue);
            }
            if (this.WhereInstanceCollation.Checked == true)
            {
                sqlQuery.Append(" AND [InstanceCollation] = '" + this.ddlInstanceCollation.SelectedValue.ToString() + "'");
            }

            if (this.WhereInstanceOSVersion.Checked == true)
            {
                sqlQuery.Append(" AND [InstanceOSVersion] = '" + this.ddlInstanceOSVersion.SelectedValue.ToString() + "'");
            }

            if (this.WhereInstanceSQLVersion.Checked == true)
            {
                sqlQuery.Append(" AND [InstanceVersion] = '" + this.ddlInstanceSQLVersion.SelectedValue.ToString() + "'");
            }

            if (this.WhereInstanceIsClustered.Checked == true)
            {
                sqlQuery.Append(" AND [InstanceIsClustered] = " + this.rdoInstanceIsClustered.SelectedValue);
            }
            if (this.WhereInstanceIsVirtual.Checked == true)
            {
                sqlQuery.Append(" AND [IsVirtual] = '" + this.rdoInstanceIsVirtual.SelectedValue.ToString()+ "'");
            }

            DataSet ds = db.ExecuteDataSet(CommandType.Text, sqlQuery.ToString());

            Session["dsInstanceList"] = ds;

            this.gvResultsInstanceSearch.DataSource = Session["dsInstanceList"];
            this.gvResultsInstanceSearch.DataBind();

            this.lblResults.Text = "Results Found: " + this.gvResultsInstanceSearch.Rows.Count.ToString();
        }
        else
        {
            //SELECT Columns
            System.Text.StringBuilder sqlQuery = new System.Text.StringBuilder("SELECT i.InstanceName, DatabaseName ");
            foreach (ListItem li in SelectList.Items)
            {
                if (li.Selected)
                {
                    if (sqlQuery.Length > 0)
                        sqlQuery.Append(", ");
                    sqlQuery.Append(li.Value);
                }
            }

            sqlQuery.Append(" FROM [Database] d with(nolock) INNER JOIN [Instance] i with(nolock) on i.Instancename = d.InstanceName WHERE 1 = 1 ");

            if (WhereInstanceName2.Checked == true)
            {
                sqlQuery.Append(" AND i.InstanceName like '%" + this.txtInstanceName2.Text.ToString() + "%'");
            }

            if (WhereDatabaseName.Checked == true)
            {
                sqlQuery.Append(" AND [DatabaseName] like '%" + this.txtDatabaseName.Text.ToString() + "%'");
            }

            if (this.WhereSizeMB.Checked == true)
            {
                sqlQuery.Append(" AND [TotalSizeMB] " + this.ddlDatabaseSizeMB.SelectedValue.ToString() + "");
            }

            if (WhereRecoveryModel.Checked == true)
            {
                sqlQuery.Append(" AND RecoveryModel = '" + this.ddlRecoveryModel.SelectedValue.ToString() + "'");
            }

            if (this.WhereTrustworthy.Checked == true)
            {
                sqlQuery.Append(" AND Trustworthy = '" + this.ddlTrustworthy.SelectedValue.ToString() + "'");
            }

            if (this.WhereDatabaseCollation.Checked == true)
            {
                sqlQuery.Append(" AND Collation = '" + this.ddlDatabaseCollation.SelectedValue.ToString() + "'");
            }

            if (this.WhereDatabaseCompatibilityLevel.Checked == true)
            {
                sqlQuery.Append(" AND CompatibilityLevel = '" + this.ddlDatabaseCompatibilityLevel.SelectedValue.ToString() + "'");
            }

            if (this.WhereDatabasePageVerify.Checked == true)
            {
                sqlQuery.Append(" AND PageVerify = '" + this.ddlDatabasePageVerify.SelectedValue.ToString() + "'");
            }

            if (this.WhereAuto.Checked == true)
            {
                sqlQuery.Append(" AND " + this.ddlAuto.SelectedValue.ToString() + " = " + this.rdoAuto.SelectedValue.ToString());
            }

            if (this.WhereFreeSpace.Checked == true)
            {
                sqlQuery.Append(" AND DataSpaceAvailablePct <= " + this.ddlDatabaseSpaceAvailablePct.SelectedValue.ToString());
            }

            if (this.WhereDatabaseIsSensitive.Checked == true)
            {
                sqlQuery.Append(" AND IsSensitive = " + this.rdoAuto.SelectedValue.ToString());
            }

            if (this.WhereDatabaseIsAudited.Checked == true)
            {
                sqlQuery.Append(" AND IsAudited = " + this.rdoAuto.SelectedValue.ToString());
            }

            DataSet ds = db.ExecuteDataSet(CommandType.Text, sqlQuery.ToString());

            this.gvResults.DataSource = ds;
            this.gvResults.DataBind();

            this.lblResults.Text = "Results Found: " + this.gvResults.Rows.Count.ToString();
        }
    }
    protected void optSearchType_SelectedIndexChanged1(object sender, EventArgs e)
    {
        this.tblSearchInstance.Visible = optSearchType.Items[0].Selected;
        this.tblSearchDatabase.Visible = optSearchType.Items[1].Selected;

        this.gvResultsInstanceSearch.Visible = optSearchType.Items[0].Selected;
        this.gvResults.Visible = optSearchType.Items[1].Selected;
    }
}
